package com.cebbank.yyi.springbootapp.service.impl;

import cn.hutool.core.util.IdUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.util.MapUtils;
import com.cebbank.yyi.springbootapp.bo.*;
import com.cebbank.yyi.springbootapp.dao.ExcelInfoMapper;
import com.cebbank.yyi.springbootapp.jingtang.utils.FileUtil;
import com.cebbank.yyi.springbootapp.poi.ExcelUtils;
import com.cebbank.yyi.springbootapp.service.ExcelService;
import com.cebbank.yyi.springbootapp.tables.CreateTablesBO;
import com.cebbank.yyi.springbootapp.tables.ExcelFormatBO;
import com.cebbank.yyi.springbootapp.tables.ExportDataBO;
import com.cebbank.yyi.springbootapp.tables.ImportDataBO;
import com.cebbank.yyi.springbootapp.tables.TableInfoBO;
import lombok.RequiredArgsConstructor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.Stream;

/**
 * @description:
 * @author: yyi
 * @version: 1.0
 **/

@Service
@RequiredArgsConstructor
public class ExcelServiceImpl implements ExcelService {

    private static final Logger log = LoggerFactory.getLogger(ExcelServiceImpl.class);

    private final ExcelInfoMapper excelInfoMapper;


    @Override
    public void insertValue(ImportDataBO importDataBO) {
        excelInfoMapper.insertExcelValues(importDataBO);
    }

    @Override
    public void createTable(List<CreateTablesBO> ddl) {
        excelInfoMapper.createTable(ddl);
    }

    @Override
    public List<CreateTablesBO> importExcel(InputStream inputStream) throws Exception {
        List<Class<?>> clazz = new ArrayList<>();
        clazz.add(HashMap.class);
        clazz.add(HashMap.class);
        ExcelUtils<Object> excelUtil = new ExcelUtils<>(clazz);
        //根据表头创建表
        Workbook workbook = WorkbookFactory.create(inputStream);
        List<CreateTablesBO> ddl = excelUtil.getDDL(workbook);
        //创建Sql语句
        excelInfoMapper.createTable(ddl);
        //读取Excel的内容，并支持将内容与字段进行映射

        List<ImportDataBO> lists = excelUtil.importsExcels(workbook, 1, ddl);
        //读取到的内容使用循环插入数据库, 后面需要修改成披批量入库
        lists.stream()
                .flatMap(importDataBO -> importDataBO.getResult().stream()
                        .map(result -> {
                            ImportDataBO newImportDataBO = new ImportDataBO(); // 根据需要可能需要深拷贝
                            newImportDataBO.setData(result);
                            newImportDataBO.setTableInfo(importDataBO.getTableInfo());
                            return newImportDataBO;
                        }))
                .forEach(excelInfoMapper::insertExcelValues);
        return ddl;
    }

    @Override
    public List<HashMap<String, Object>> executeSql(String sql) {
        return excelInfoMapper.executeSql(sql);
    }

    @Override
    public void dataToExcel(List<HashMap<String, Object>> list, ExportDataBO exportDataBo, String excelFilePath) {
        int row = 0;
        // 创建一个新的工作簿
        Workbook workbook = new SXSSFWorkbook();
        Map<String, CellStyle> styles = createStyles(workbook);
        // 创建一个工作表
        Sheet sheet = workbook.createSheet(exportDataBo.getSheetName());
        if (StrUtil.isNotEmpty(exportDataBo.getTitleName())) {
            int titleLastCol = list.get(0).size() - 1;
            Row titleRow = sheet.createRow(row);
            row++;
            titleRow.setHeightInPoints(30);
            Cell titleCell = titleRow.createCell(0);
            titleCell.setCellStyle(styles.get("title"));
            titleCell.setCellValue(exportDataBo.getTitleName());
            sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(), titleRow.getRowNum(), titleLastCol));
        }
        if (exportDataBo.getHeaders() != null && !exportDataBo.getHeaders().isEmpty()) {
            Row titleRow = sheet.createRow(row);
            row++;
            int dataIndex = 0;
            //添加header头
            for (int i = 0; i < exportDataBo.getHeaders().size(); i++) {
                //创建cell
                Cell cell = titleRow.createCell(dataIndex++);
                cell.setCellValue(exportDataBo.getHeaders().get(i));
                cell.setCellStyle(styles.get("header"));
            }
        }
        // 创建数据行
        for (int i = 0; i < list.size(); i++) {
            Row dataRow = sheet.createRow(row);
            row++;
            int dataIndex = 0;
            Map<String, Object> map = list.get(i);
            for (Map.Entry<String, Object> entry : map.entrySet()) {
                Object value = entry.getValue();
                Cell cell = dataRow.createCell(dataIndex++);
                if (value instanceof String) {
                    cell.setCellValue((String) value);
                } else if (value instanceof Number) {
                    cell.setCellValue(((Number) value).doubleValue());
                } else if (value instanceof Boolean) {
                    cell.setCellValue((Boolean) value);
                } else {
                    cell.setCellValue(value.toString());
                }
                cell.setCellStyle(styles.get("data"));
            }

        }
        // 将工作簿写入文件
        try (FileOutputStream fileOut = new FileOutputStream(excelFilePath)) {
            workbook.write(fileOut);
            workbook.close();
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public void mergeCell(InputStream is, String excelFilePath, int startRow, ExcelFormatBO excelFormat) throws IOException {
        Workbook workbook = WorkbookFactory.create(is);
        Map<String, CellStyle> styles = createStyles(workbook);

        for (int j = 0; j < workbook.getNumberOfSheets(); j++) {
            Sheet sheet = workbook.getSheetAt(j);
            for (int col = 0; col < sheet.getRow(startRow).getLastCellNum(); col++) {
                int firstRow = startRow;
                int lastRow = startRow;
                for (int row = startRow + 1; row <= sheet.getLastRowNum(); row++) {
                    Cell firstCell = sheet.getRow(firstRow).getCell(col);
                    if (excelFormat.getColumns().contains(col) || excelFormat.getRows().contains(row)) {
                        firstCell.setCellStyle(styles.get("tag"));
                    } else {
                        firstCell.setCellStyle(styles.get("data"));
                    }
                    Cell currentCell = sheet.getRow(row).getCell(col);
                    if (excelFormat.getColumns().contains(col) || excelFormat.getRows().contains(row)) {
                        currentCell.setCellStyle(styles.get("tag"));
                    } else {
                        currentCell.setCellStyle(styles.get("data"));
                    }

                    if (firstCell.getStringCellValue().equals(currentCell.getStringCellValue())) {
                        lastRow = row;
                    } else {
                        if (lastRow > firstRow) {
                            sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, col, col));
                        }
                        firstRow = row;
                        lastRow = row;
                    }
                }

                if (lastRow > firstRow) {
                    sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, col, col));
                }
            }
        }
        try (FileOutputStream fileOut = new FileOutputStream(excelFilePath)) {
            workbook.write(fileOut);
            workbook.close();
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        }
    }


    @Override
    public void mergeSameCellsInColumn(InputStream is, MergeCellBO mergeCellBO, String excelFilePath) throws IOException {
        Workbook workbook = WorkbookFactory.create(is);
        Map<String, CellStyle> cellStyle = cellStyleMap(workbook, mergeCellBO);
        for(int i = 0; i < workbook.getNumberOfSheets(); i++) {
            Sheet sheet = workbook.getSheetAt(i);
            int lastRowNum = sheet.getLastRowNum();
            if (lastRowNum < 1) {
                return;
            }
            for (Integer columnIndex : mergeCellBO.getColumns()) {
                int startRow = 1;
                String previousValue = sheet.getRow(startRow).getCell(columnIndex).getStringCellValue();
                for (int rowNum = 1; rowNum <= lastRowNum; rowNum++) {
                    Row row = sheet.getRow(rowNum);
                    String currentValue = row.getCell(columnIndex).getStringCellValue();
                    if (!currentValue.equals(previousValue)) {
                        if (rowNum - 1 > startRow) {
                            sheet.addMergedRegion(new CellRangeAddress(startRow, rowNum - 1, columnIndex, columnIndex));
                        }
                        startRow = rowNum;
                        previousValue = currentValue;
                    }
                }
                // 合并最后一组相同的单元格
                if (lastRowNum > startRow) {
                    sheet.addMergedRegion(new CellRangeAddress(startRow, lastRowNum, columnIndex, columnIndex));
                }
            }
            //给对应的设置样式
            int m = 0;
            int n = 0;
            for(Row row : sheet) {
                for (Cell cell : row) {
                    if (cellStyle.containsKey("row" + m)) {
                        cell.setCellStyle(cellStyle.get("row" + m));
                    }
                    if (cellStyle.containsKey("column" + n)) {
                        cell.setCellStyle(cellStyle.get("column" + n));
                    }
                    m++;
                }
                m = 0;
                n++;
            }
            
        }
        try (FileOutputStream fileOut = new FileOutputStream(excelFilePath)) {
            workbook.write(fileOut);
            workbook.close();
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        }
    }

    //缠粉文件
    @Override
    public void splitByParam(InputStream inputStream, SplitFileBO fileBO) throws Exception {
        List<String> splitList = fileBO.getSplitField();
        String type = fileBO.getSplitType();
        //读取Excel中的数据
        List<Class<?>> clazz = new ArrayList<>();
        clazz.add(Object.class);
        ExcelUtils<Object> excelUtil = new ExcelUtils<>(clazz);
        Workbook workbook = WorkbookFactory.create(inputStream);
        List<ImportDataBO> lists = excelUtil.importsExcels(workbook, 1, null);
        List<List<Object>> collect = lists.stream().map(ImportDataBO::getResult)
                .collect(Collectors.toList()).get(0);
        List<String> columnHeaders = lists.get(0).getHeaders();
        List<HashMap<String, Object>> result = new ArrayList<>();
        for (List<Object> objects : collect) {
            HashMap<String, Object> str = new HashMap<>();
            for (int i = 0; i < objects.size(); i++) {
                str.put(columnHeaders.get(i), objects.get(i).toString());
            }
            result.add(str);
        }
        List<List<String>> headers = new ArrayList<>();
        headers.add(columnHeaders);
        String resultPath = "D://code//";
        for (String s : splitList) {
            Map<Object, List<HashMap<String, Object>>> collected = result.stream()
                    .collect(Collectors.groupingBy(map -> map.get(s)));
            Map<String, List<HashMap<String, Object>>> collects = collected.entrySet().stream()
                    .collect(Collectors.toMap(
                            entry -> entry.getKey().toString(), Map.Entry::getValue));
            resultPath = resultPath + s + "//";
            File file = new File(resultPath);
            if (!file.exists()) {
                file.mkdirs();
            }
            List<CellStyleBO> listStyle = new ArrayList<>();
            listStyle.add(new CellStyleBO());
            if (type.equals("0")) {
                dataToExcel(new HashMap<>(collects), headers, listStyle, resultPath + "//" + s + ".xlsx");
            } else {
                //大于1的Map需要拆分成
                if (collects.size() > 1) {
                    List<Map<String, List<HashMap<String, Object>>>> listOfMaps = new ArrayList<>();
                    for (Map.Entry<String, List<HashMap<String, Object>>> entry : collects.entrySet()) {
                        Map<String, List<HashMap<String, Object>>> singleEntryMap = new HashMap<>();
                        singleEntryMap.put(entry.getKey(), entry.getValue());
                        listOfMaps.add(singleEntryMap);
                    }
                    for (Map<String, List<HashMap<String, Object>>> stringListMap : listOfMaps) {
                        dataToExcel(new HashMap<>(stringListMap), headers, listStyle, resultPath + "//" + stringListMap.keySet().iterator().next() + ".xlsx");
                    }

                }
            }

        }
        System.err.println(collect);
    }


    /**
     * 针对不同的行 不同的列创建的样式
     * **/

    private Map<String, CellStyle> cellStyleMap(Workbook workbook, MergeCellBO mergeCellBO) {
        HashMap<String, CellStyle> styles = new HashMap<>();
        if (mergeCellBO.getColumnStyle() != null) {
            for (int i = 0; i < mergeCellBO.getColumnStyle().size(); i++) {
                //调用格式化输出
                CellStyle style = workbook.createCellStyle();
                Font dataFont = workbook.createFont();
                dataFont.setFontName(mergeCellBO.getColumnStyle().get(i).getFontFamily() == null ? "Arial" : mergeCellBO.getColumnStyle().get(i).getFontFamily());
                dataFont.setFontHeightInPoints(mergeCellBO.getColumnStyle().get(i).getFontSize() == 0.0 ? (short) 14.0 : mergeCellBO.getColumnStyle().get(i).getFontSize());
                dataFont.setColor(mergeCellBO.getColumnStyle().get(i).getFontColor() == null ? IndexedColors.BLACK.getIndex() : mergeCellBO.getColumnStyle().get(i).getFontColor().getIndex());
                style.setFont(dataFont);
                style.setFillForegroundColor(mergeCellBO.getColumnStyle().get(i).getBackgroundColor() == null ? IndexedColors.WHITE.getIndex() : mergeCellBO.getColumnStyle().get(i).getBackgroundColor().getIndex());
                style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                style.setVerticalAlignment(mergeCellBO.getColumnStyle().get(i).getVerticalAlignment() == null ? VerticalAlignment.CENTER : mergeCellBO.getColumnStyle().get(i).getVerticalAlignment());
                style.setAlignment(mergeCellBO.getColumnStyle().get(i).getHorizontalAlignment() == null ? HorizontalAlignment.CENTER : mergeCellBO.getColumnStyle().get(i).getHorizontalAlignment());
                //单元格四周的线设置为默认
                style.setBorderRight(BorderStyle.THIN);
                style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
                style.setBorderLeft(BorderStyle.THIN);
                style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
                style.setBorderTop(BorderStyle.THIN);
                style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
                style.setBorderBottom(BorderStyle.THIN);
                style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
                styles.put("column" + i, style);
            }
        }
        if (mergeCellBO.getRowStyle() != null) {
            for (int i = 0; i < mergeCellBO.getRowStyle().size(); i++) {
                //调用格式化输出
                CellStyle style = workbook.createCellStyle();
                Font dataFont = workbook.createFont();
                dataFont.setFontName(mergeCellBO.getRowStyle().get(i).getFontFamily() == null ? "Arial" : mergeCellBO.getRowStyle().get(i).getFontFamily());
                dataFont.setFontHeightInPoints(mergeCellBO.getRowStyle().get(i).getFontSize() == 0.0 ? (short) 14.0 : mergeCellBO.getRowStyle().get(i).getFontSize());
                dataFont.setColor(mergeCellBO.getRowStyle().get(i).getFontColor() == null ? IndexedColors.BLACK.getIndex() : mergeCellBO.getRowStyle().get(i).getFontColor().getIndex());
                style.setFont(dataFont);
                style.setFillForegroundColor(mergeCellBO.getRowStyle().get(i).getBackgroundColor() == null ? IndexedColors.WHITE.getIndex() : mergeCellBO.getRowStyle().get(i).getBackgroundColor().getIndex());
                style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                style.setVerticalAlignment(mergeCellBO.getRowStyle().get(i).getVerticalAlignment() == null ? VerticalAlignment.CENTER : mergeCellBO.getRowStyle().get(i).getVerticalAlignment());
                style.setAlignment(mergeCellBO.getRowStyle().get(i).getHorizontalAlignment() == null ? HorizontalAlignment.CENTER : mergeCellBO.getRowStyle().get(i).getHorizontalAlignment());
                //单元格四周的线设置为默认
                style.setBorderRight(BorderStyle.THIN);
                style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
                style.setBorderLeft(BorderStyle.THIN);
                style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
                style.setBorderTop(BorderStyle.THIN);
                style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
                style.setBorderBottom(BorderStyle.THIN);
                style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
                styles.put("row" + i, style);
            }
        }
        return styles;
    }
    private Map<String, CellStyle> createStylesByParams(Workbook workbook, Sheet sheet, List<CellStyleBO> styleList) {
        Map<String, CellStyle> styles = new HashMap<>();
        for (int i = 0; i < styleList.size(); i++) {
            CellStyle style = workbook.createCellStyle();
            CellStyleBO styleBO = styleList.get(i);
            sheet.setColumnWidth(i, styleBO.getWidth() == 0f ? 20 * 256 : (int) styleBO.getWidth());
            style.setAlignment(styleBO.getHorizontalAlignment() == null ? HorizontalAlignment.CENTER : styleBO.getHorizontalAlignment());
            style.setVerticalAlignment(styleBO.getVerticalAlignment() == null ? VerticalAlignment.CENTER : styleBO.getVerticalAlignment());
            //单元格四周的线设置为默认
            style.setBorderRight(BorderStyle.THIN);
            style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
            style.setBorderLeft(BorderStyle.THIN);
            style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
            style.setBorderTop(BorderStyle.THIN);
            style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
            style.setBorderBottom(BorderStyle.THIN);
            style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
            //titleRow.setHeightInPoints(30);
            //设置字体
            Font dataFont = workbook.createFont();
            dataFont.setFontName(styleBO.getFontFamily() == null ? "Arial" : styleBO.getFontFamily());
            dataFont.setFontHeightInPoints(styleBO.getFontSize() == 0.0 ? (short) 14.0 : styleBO.getFontSize());
            dataFont.setColor(styleBO.getFontColor() == null ? IndexedColors.BLACK.getIndex() : styleBO.getFontColor().getIndex());
            style.setFont(dataFont);
            style.setFillForegroundColor(styleBO.getBackgroundColor() == null ? IndexedColors.WHITE.getIndex() : styleBO.getBackgroundColor().getIndex());
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            styles.put("style" + i, style);
        }

        return styles;
    }

    private Map<String, CellStyle> createStyles(Workbook wb) {
        // 写入各条记录,每条记录对应excel表中的一行
        Map<String, CellStyle> styles = new HashMap<>();
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        Font dataFont = wb.createFont();
        dataFont.setFontName("Arial");
        dataFont.setFontHeightInPoints((short) 10);
        style.setFont(dataFont);
        styles.put("data", style);
        style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        Font totalFont = wb.createFont();
        totalFont.setFontName("Arial");
        totalFont.setFontHeightInPoints((short) 10);
        style.setFont(totalFont);
        styles.put("total", style);
        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.AUTOMATIC.index);
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font headerFont = wb.createFont();
        headerFont.setFontName("Arial");
        headerFont.setFontHeightInPoints((short) 10);
        headerFont.setBold(true);
        headerFont.setColor(IndexedColors.WHITE.index);
        style.setFont(headerFont);
        styles.put("header", style);
        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.YELLOW1.index);
        style.setFillPattern(FillPatternType.ALT_BARS);
        Font att = wb.createFont();
        att.setFontName("Calibri");
        att.setFontHeightInPoints((short) 14);
        att.setBold(true);
        att.setColor(IndexedColors.WHITE.index);
        style.setFont(headerFont);
        styles.put("tag", style);
        style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        Font titleFont = wb.createFont();
        titleFont.setFontName("Arial");
        titleFont.setFontHeightInPoints((short) 16);
        titleFont.setBold(true);
        style.setFont(titleFont);
        style.setFillForegroundColor(IndexedColors.YELLOW1.index);
        style.setFillPattern(FillPatternType.ALT_BARS);
        styles.put("title", style);
        return styles;
    }


    @Override
    public void mergeAllExcel(List<MultipartFile> files, String excelFilePath) {
        List<List<Object>> collect = files.stream()
                .flatMap(file -> {
                    try (InputStream inputStream = file.getInputStream()) {
                        List<Class<?>> clazz = new ArrayList<>();
                        clazz.add(Object.class);
                        ExcelUtils<Object> excelUtil = new ExcelUtils<>(clazz);
                        Workbook workbook = WorkbookFactory.create(inputStream);
                        List<ImportDataBO> lists = excelUtil.importsExcels(workbook, 1, null);
                        return lists.stream().map(ImportDataBO::getResult);
                    } catch (Exception e) {
                        log.error("mergeAllExcel捕获到异常Exception:", e);
                        return Stream.empty();
                    }
                }).flatMap(List::stream)
                .collect(Collectors.toList());
        log.info("合并后的Excel: {}", collect);
    }


  
    public List<CreateTablesBO> mergeAllExcels(List<MultipartFile> files) throws Exception {
        List<Class<?>> clazz = new ArrayList<>();
        clazz.add(HashMap.class);
        clazz.add(HashMap.class);
        ExcelUtils<Object> excelUtil = new ExcelUtils<>(clazz);
        //针对同数据结构的Excel导入到同一个表
        Workbook workbook = WorkbookFactory.create(files.get(0).getInputStream());
        List<CreateTablesBO> ddl = excelUtil.getDDL(workbook);
        //创建Sql语句
        excelInfoMapper.createTable(ddl);
        List<ImportDataBO> listResult = new ArrayList<>();
        //根据表头创建表
        for (MultipartFile file : files) {
            //读取Excel的内容，并支持将内容与字段进行映射
            workbook = WorkbookFactory.create(file.getInputStream());
            List<ImportDataBO> lists = excelUtil.importsExcels(workbook, 1, ddl);
            //读取到的内容使用循环插入数据库, 后面需要修改成披批量入库
            listResult.addAll(lists);
        }
        listResult.stream()
                .flatMap(importDataBO -> importDataBO.getResult().stream()
                        .map(result -> {
                            ImportDataBO newImportDataBO = new ImportDataBO(); // 根据需要可能需要深拷贝
                            newImportDataBO.setData(result);
                            newImportDataBO.setTableInfo(importDataBO.getTableInfo());
                            return newImportDataBO;
                        }))
                .forEach(excelInfoMapper::insertExcelValues);

        return ddl;
    }
    

    @Override
    public List<String> getTableFields(String tableName) {
        List<HashMap<String, String>> tableFiled = excelInfoMapper.getTableFiled(tableName);
        return tableFiled.stream()
                .map(it -> it.get("Field"))
                .collect(Collectors.toList());
    }

    @Override
    public void exportExcelFields(ExportFiledsBO exportFiledsBO) {
        List<String> fields = exportFiledsBO.getFields();
        String tableName = exportFiledsBO.getTableName();
        //数据表中获取指定表中的数据
        List<HashMap<String, Object>> tableData = excelInfoMapper.getTableData(fields, tableName);
        HashMap<String, List<HashMap<String, Object>>> maps = new HashMap<>();
        maps.put(exportFiledsBO.getSheetName(), tableData);
        dataToExcel(maps,
                Collections.singletonList(exportFiledsBO.getFieldsName()),
                exportFiledsBO.getCellStyles(), "D://code//test.xlsx");
    }

    @Override
    public void exportExcelFieldsSplit(ExportFiledExtendBO exportFiledsBO) {
        List<String> fields = exportFiledsBO.getFields();
        String tableName = exportFiledsBO.getTableName();
        //数据表中获取指定表中的数据
        List<HashMap<String, Object>> tableData = excelInfoMapper.getTableData(fields, tableName);
        Map<String, List<HashMap<String, Object>>> collect = tableData.stream()
                .collect(
                        Collectors.groupingBy(map -> map.get(exportFiledsBO.getSplit()).toString())
                );

        //判断是按文件倒出还是按Excel导出
        if(exportFiledsBO.getSplitType().equals("0")) {
            for (Map.Entry<String, List<HashMap<String, Object>>> entry : collect.entrySet()) {
                String fileName = String.format("%s.xlsx", FileUtil.getSnowId());
                HashMap<String, List<HashMap<String, Object>>> maps = new HashMap<>();
                List<HashMap<String, Object>> value = entry.getValue();
                maps.put(exportFiledsBO.getSheetName(), value);
                dataToExcel(maps,
                        Collections.singletonList(exportFiledsBO.getFieldsName()),
                        exportFiledsBO.getCellStyles(), "D://code//" + fileName);
            }
           
        } else {
            HashMap<String, List<HashMap<String, Object>>> maps = new HashMap<>(collect);
            dataToExcel(maps,
                    Collections.singletonList(exportFiledsBO.getFieldsName()),
                    exportFiledsBO.getCellStyles(), "D://code//dateToExcel.xlsx");
        }


    }


    private void dataToExcel(HashMap<String, List<HashMap<String, Object>>> maps,
                             List<List<String>> headers,
                             List<CellStyleBO> cellStyles,
                             String excelFilePath) {
        // 创建一个新的工作簿
        Workbook workbook = new SXSSFWorkbook();
        Map<String, CellStyle> styles = createStyles(workbook);
        // 创建一个工作表
        int k = 0;
        for (Map.Entry<String, List<HashMap<String, Object>>> entrw : maps.entrySet()){
            int row = 0;
            Sheet sheet = workbook.createSheet(entrw.getKey());
            Map<String, CellStyle> stylesByParams = createStylesByParams(workbook, sheet, cellStyles);
            if (StrUtil.isNotEmpty(entrw.getKey())) {
                assert headers != null;
                int titleLastCol;
                if (headers.size() == 1) {
                    titleLastCol = headers.get(0).size() - 1;
                } else {
                    titleLastCol = headers.get(k).size() - 1;
                }

                Row titleRow = sheet.createRow(row);
                row++;
                titleRow.setHeightInPoints(30);
                Cell titleCell = titleRow.createCell(0);
                titleCell.setCellStyle(styles.get("title"));
                titleCell.setCellValue(entrw.getKey());
                sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(), titleRow.getRowNum(), titleLastCol));
            }
            if (headers != null) {
                Row titleRow = sheet.createRow(row);
                row++;
                int dataIndex = 0;
                //添加header头
                for (int i = 0; i < headers.get(0).size(); i++) {
                    //创建cell
                    Cell cell = titleRow.createCell(dataIndex++);
                    cell.setCellValue(headers.get(0).get(i));
                    cell.setCellStyle(styles.get("header"));
                }
            }
            // 创建数据行
            for (int i = 0; i < entrw.getValue().size(); i++) {
                Row dataRow = sheet.createRow(row);
                //设置单元格样式
                dataRow.setHeightInPoints(25);
                //dataRow.setHeightInPoints(cellStyles.get(i).getHeight() == 0.0f ? 30 : cellStyles.get(i).getHeight());
                row++;
                int dataIndex = 0;
                Map<String, Object> map =entrw.getValue().get(i);
                int index = 0;
                for (Map.Entry<String, Object> entr : map.entrySet()) {
                    Object value = entr.getValue();
                    Cell cell = dataRow.createCell(dataIndex++);
                    if (value instanceof String) {
                        cell.setCellValue((String) value);
                    } else if (value instanceof Number) {
                        cell.setCellValue(((Number) value).doubleValue());
                    } else if (value instanceof Boolean) {
                        cell.setCellValue((Boolean) value);
                    } else {
                        cell.setCellValue(value.toString());
                    }
                    cell.setCellStyle(stylesByParams.get("style" + index));
                    if (cellStyles.size() > 1) {
                        index++;
                    }
                }

            }
            k++;
        }



        // 将工作簿写入文件
        try (FileOutputStream fileOut = new FileOutputStream(excelFilePath)) {
            workbook.write(fileOut);
            workbook.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public void exportExcelSheet(List<TableInfoBO> tablesNames) {
        //数据库中查询数据
        HashMap<String, List<HashMap<String, Object>>> result = new HashMap<>();
        for (TableInfoBO tableInfo : tablesNames) {
            List<HashMap<String, Object>> tableData = excelInfoMapper.getTableData(tableInfo.getColumns(), tableInfo.getTableName());
            result.put(tableInfo.getSheetName(), tableData);
        }
        List<List<String>> headers = tablesNames.stream().map(TableInfoBO::getHeaders).collect(Collectors.toList());
        //将数据导出到Excel
        dataToExcel(result, headers, Collections.singletonList(new CellStyleBO()), "D://code//testSheet.xlsx");
    }

    @Override
    public void exportExcelByTemplate(HashMap<String, Object> data, String excelPath) {
        // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
        String templateFileName = "D://code//example.xlsx";
        // 这里 会填充到第一个sheet， 然后文件流会自动关闭
        Map<String, Object> map = MapUtils.newHashMap();
        EasyExcel.write("D://code//template.xlsx").withTemplate(templateFileName).sheet().doFill(data);
    }

    @Override
    public List<String> getAllTables() {
        return excelInfoMapper.getAllTables();
    }

    @Override
    public void readExcel(InputStream inputStream, String sql, int num) {
        ExcelUtils<EmployeeBO> excelUtils = new ExcelUtils<>(Collections.singletonList(EmployeeBO.class));
        //执行sql,完成
        try {
            List<EmployeeBO> lists = excelUtils.importExcel(inputStream);
            for (EmployeeBO employeeBo : lists) {
                excelInfoMapper.insertDepart(employeeBo);
            }
            excelInfoMapper.executeSql(sql);
        } catch (Exception e) {
            log.error("输出错误信息:", e);
        }

    }

}
